﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jumpton.SFA.Server.Entity;
using Jumpton.Persistent;
using Jumpton.Data;
using System.Data;

namespace Jumpton.SFA.Server.BusinessRule
{
    public class OrderLineManager: BusinessLogic
    {

        public DataTable QueryCPR(string tableName, decimal visitID)
        {
            string sql = string.Format(@"select   p.code
                                                ,p.description
                                                ,(case cast(e.value as varchar) when 'Y' then '√' else e.value end) as value
                                       from {0} e 
                                            inner join products p
                                                on e.PRODUCTS_ID=p.id
                                       where e.VISITID=convert(numeric(30,0),'{1}')", tableName, visitID);

            return SqlData.OpenSql(sql).Tables[0];
        }

        public DataTable QueryInv(decimal visitID)
        {
            string sql = @"select	PRODUCTS.Code
		                        ,PRODUCTS.Description
		                        ,'箱' UOM
		                        ,INV.Value
                        from CPR_Last_Time_CASE_INVENTORY INV
	                        inner join PRODUCTS
		                        on PRODUCTS.ID=INV.ProductID
                        where value>0 and VISITID='" + visitID + "'";
            sql += @"      union
                        select	PRODUCTS.Code
		                        ,PRODUCTS.Description
		                        ,'支' UOM
		                        ,INV.Value
                        from CPR_Last_Time_PIECE_INVENTORY INV
	                        inner join PRODUCTS
		                        on PRODUCTS.ID=INV.ProductID
                        where value>0 and VISITID='" + visitID + "'";

            return SqlData.OpenSql(sql).Tables[0];
        }
        public ORDER_LINES Load(decimal ID)
        {
            return (ORDER_LINES)ObjectRow.Load(typeof(ORDER_LINES), string.Format("this.ID={0}", ID));
        }
        public ORDER_LINES Load(string ORDER_NO, string PRODUCT_ID,string promotion_id)
        {
            return (ORDER_LINES)ObjectRow.Load(typeof(ORDER_LINES), "ORDER_NO='" + ORDER_NO + "' and PRODUCT_ID='" + PRODUCT_ID + "' and promotion_id='" + promotion_id+"'");
        }

        public static ObjectTable GetOrderLinesByOrderNo(string orderNo)
        {
            return new ObjectTable(typeof(ORDERLINEVIEW), string.Format("a.ORDER_NO= '{0}'", orderNo));
        }

        public static DataSet GetCustomerProductListByID(decimal customerID,string whereCondition)
        {
            return SqlData.OpenSP("sp_GetProductListForOneCustomer",customerID,whereCondition);
        }

        public void Save(ORDER_LINES line)
        {
            if (line != null)
            {
                try
                {
                    line.Save();
                }
                catch (Exception err)
                {
                    message = err.Message;throw err;
                }
            }
        }

        public static void DeleteOneLineByLineID(decimal lineID)
        {
            SqlData.ExecuteSP("sp_DeleteOneLineByLineId", lineID);
        }

        public static DataSet GetLineByOrderID(string orderID)
        {
            return SqlData.OpenSP("[SP_GET_ORDERLINE_BY_ORDER_ID]", orderID);
        }

        public bool ExistOrderLine(string ORDER_NO, int PRODUCT_ID)
        {
            return new ObjectTable(typeof(ORDER_LINES), "ORDER_NO in (select distinct ORDER_NO from ORDER_LINES where ORDER_NO='" + ORDER_NO + "' and PRODUCT_ID='" + PRODUCT_ID.ToString() + "')").Count > 0 ? true : false;
        }

        public DataTable GetOrderLineList(String orderNo)
        {

            String sql = String.Format(@"select * from ORDER_LINES where ORDER_NO='{0}' and CANCELLED='N' ", orderNo);
            return Jumpton.Data.SqlData.OpenSql(sql).Tables[0];
        }
    }
}
